package itrans;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.IOException;
import java.io.PrintWriter;
import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

import org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS;
import org.apache.commons.dbcp.datasources.*;

public class SaveStop extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
	throws IOException, ServletException
	{
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		out.println("Sorry!!!");
	}
	
	public void doPost(HttpServletRequest request, HttpServletResponse response)
	throws IOException, ServletException
	{
		process(request, response);
	}
	
	public void process(HttpServletRequest request, HttpServletResponse response)
	throws IOException, ServletException{
    
        HttpSession session = request.getSession(true);
        StopBean stop = (StopBean) session.getAttribute(PublicConstants.STOPBEAN_ATTR);
        UserBean user = (UserBean) session.getAttribute(PublicConstants.USERBEAN_ATTR);
        saveStop(stop, user);
	}
	
	// DUPLICATE CODE very bad.
	public boolean checkStopExists(String field, String arg, int userId)
	{
		Connection con;
		boolean exists = false;
		String searchQuery = "SELECT * FROM MyStops WHERE " + field + " = ? and user_id = ? ";
		try {
			DataSource dbcp = (DataSource)getServletContext().getAttribute("dbpool");
            con = dbcp.getConnection();
			PreparedStatement query = con.prepareStatement(searchQuery);
			query.setString(1, arg);
			query.setInt(2, userId);
			ResultSet rs = query.executeQuery();
			
			// it's not empty
			if (rs.next()){
				exists = true;
			}
			
			rs.close();
			query.close();
			con.close();
		}
		catch(SQLException ex) {
          System.err.println("SQLException: " + ex.getMessage());
        }
        catch (Exception e) {
          e.printStackTrace();
        }
        return exists;
	}
	
	private int saveStop(StopBean stop, UserBean user)
	{
        Connection con;
        int rowsEffected = 0;
        
        String routeid = "";
        String route_title = "";
        String runid = stop.getRunId();
        String run_title = "";
        String stopid = stop.getStopId();
        String stop_title = "";
        int userId = user.getId();
        
        if (checkStopExists("stopid", stopid, userId))
        {
			return rowsEffected;
		}
		
		String searchQuery = "select ro.id 'routeid', ro.display_name 'route_title', r.id 'runid', r.display_name 'run_title', s.id 'stopid', s.display_name 'stop_title' from Stops s join Runs r on s.runid = r.id join Routes ro on r.route_id = ro.id where s.id=? and r.id=?";
		try {
			DataSource dbcp = (DataSource)getServletContext().getAttribute("dbpool");
            con = dbcp.getConnection();

            PreparedStatement query = con.prepareStatement(searchQuery);
			query.setString(1, stopid);
			query.setString(2, runid);
			
			ResultSet rs = query.executeQuery();
			
			// for each returned column
			while (rs.next()) {
				routeid = rs.getString("routeid");
				route_title = rs.getString("route_title");
				runid = rs.getString("runid");
				run_title = rs.getString("run_title");
				stopid = rs.getString("stopid");
				stop_title = rs.getString("stop_title");
            }
        
			String insertQuery = "INSERT INTO MyStops (routeid, route_title, runid, run_title, stopid, stop_title, user_id) VALUES"
                + "(?,?,?,?,?,?,?)";

            PreparedStatement insertQueryP = con.prepareStatement(insertQuery);

            // populate the prepared statement with the CD properties
            insertQueryP.setString(1, routeid);
            insertQueryP.setString(2, route_title);
            insertQueryP.setString(3, runid);
            insertQueryP.setString(4, run_title);
            insertQueryP.setString(5, stopid);
            insertQueryP.setString(6, stop_title);
            insertQueryP.setInt(7, userId);
            rowsEffected = insertQueryP.executeUpdate();

            // close prep statement
            insertQueryP.close();

            //close db connection
            con.close();

        }

        catch(SQLException ex) {
          System.out.println("SQLException: " + ex.getMessage());
        }
        catch (Exception e) {
          e.printStackTrace();
        }

        return rowsEffected;
    }
}
